****************************************************************************************************
* Purpose: Analyze diabetes and mortality data
* Written by: Hunter Green and David Flood
* Last updated: 2024-12-28
* Stata version: 18.0
****************************************************************************************************

* Toggle for whether David is working on this
global David = "F"


****************************************************************************************************
* Options, global macros, log
****************************************************************************************************
* Options
version 18.0
clear all
set more off
set varabbrev off
pause on

* Use the macro $S_DATE to save files with current date
global date : disp %tdCCYY.NN.DD date("$S_DATE","DMY")

* Global folder macros
if "${David}" == "T" {	//David add your folder paths here
	* paper
	global paper_code "/Users/dcflood/Library/CloudStorage/Dropbox-UniversityofMichigan/David Flood/HPACC/Aging projects/HRS diabetes mortality/Code"
	global paper_data "/Users/dcflood/Library/CloudStorage/Dropbox-UniversityofMichigan/David Flood/HPACC/Aging projects/HRS diabetes mortality/Data"
	global paper_tables "/Users/dcflood/Library/CloudStorage/Dropbox-UniversityofMichigan/David Flood/HPACC/Aging projects/HRS diabetes mortality/Tables"
	global paper_figures "/Users/dcflood/Library/CloudStorage/Dropbox-UniversityofMichigan/David Flood/HPACC/Aging projects/HRS diabetes mortality/Figures"
}
else {
	* paper
	global paper_code "/Users/dcflood/Library/CloudStorage/Dropbox-UniversityofMichigan/David Flood/HPACC/Aging projects/HRS diabetes mortality/Code"
	global paper_data "/Users/dcflood/Library/CloudStorage/Dropbox-UniversityofMichigan/David Flood/HPACC/Aging projects/HRS diabetes mortality/Data"
	global paper_tables "/Users/dcflood/Library/CloudStorage/Dropbox-UniversityofMichigan/David Flood/HPACC/Aging projects/HRS diabetes mortality/Tables"
	global paper_figures "/Users/dcflood/Library/CloudStorage/Dropbox-UniversityofMichigan/David Flood/HPACC/Aging projects/HRS diabetes mortality/Figures"
}

* Open log
capture log close
log using "${paper_code}/02_diab-mort-analysis.log", replace


****************************************************************************************************
* Load data
****************************************************************************************************
use "${paper_data}/Raw/diab_mort_clean.dta", clear


****************************************************************************************************
****************************************************************************************************
* Table 1
****************************************************************************************************
****************************************************************************************************
* Create excel doc
putexcel set "${paper_tables}/HRS diabetes mortality analysis ${date}.xlsx", sheet("Table 1") replace

* Table formatting
* Lines
putexcel A3:F3, border(top)
putexcel A3:F3, border(bottom)
putexcel A23:F23, border(bottom)

* Font
putexcel A1:F23, font("Arial",10)

* Column width
mata: b = xl()
mata: b.load_book("${paper_tables}/HRS diabetes mortality analysis ${date}.xlsx")
mata: b.set_sheet("Table 1")
mata: b.set_column_width(1,1,45)
mata: b.set_column_width(2,6,20)

* Center justify
putexcel B3:F23, hcenter

* Right justify
putexcel A13:A15, right
putexcel A18:A21, right

* Make table frame
* Title
putexcel A1 = "Table 1: Cohort characteristics"

* Top rows
putexcel B3 = "China (CHARLS)"
putexcel C3 = "England (ELSA)"
putexcel D3 = "Mexico (MHAS)"
putexcel E3 = "South Africa (HAALSI)"
putexcel F3 = "United States (HRS)"

* First column *************************************************************************************
putexcel A4 = "Survey characteristics"
putexcel A5 = "Years of data collection"
putexcel A6 = "Sample size, n"
putexcel A7 = "Deaths, n"
putexcel A8 = "Follow-up period (years), median (IQR)"
putexcel A9 = "Respondent characteristics"
putexcel A10 = "Age (years), median (IQR)"
putexcel A11 = "Female, % (95% CI)"
putexcel A12 = "Education, % (95% CI)"
putexcel A13 = "Less than upper secondary"
putexcel A14 = "Upper secondary and vocational"
putexcel A15 = "Tertiary"
putexcel A16 = "Current smoker, % (95% CI)"
putexcel A17 = "BMI, % (95% CI)"
putexcel A18 = "<18.5 (Underweight)"
putexcel A19 = "18.5-24.9 (Normal)"
putexcel A20 = "25.0-29.9 (Overweight)"
putexcel A21 = ">=30 (Obese)"
putexcel A22 = "Diabetes (diagnosed and undiagnosed), % (95% CI)"
putexcel A23 = "Diagnosed among all with diabetes, % (95% CI)"

** Fill in results
** Second column (CHARLS) *********************************************************
svyset [pw = c_weight]

* Years of data collection
putexcel B5 = "2011 to 2019"

* Sample size
tab nonmiss if nonmiss == 1 & study == 3, matcell(mat_x)
putexcel B6 = mat_x[1,1], nformat("#,###")

* Deaths
tab censordied if nonmiss == 1 & study == 3, matcell(mat_x)
putexcel B7 = mat_x[2,1], nformat("#,###")

* Follow-up period (years)
summ followtime_y if nonmiss == 1 & study == 3, d
local loc_x = string(r(p50),"%9.1f") + " (" + string(r(p25),"%9.1f") + " - " + string(r(p75),"%9.1f") + ")"
putexcel B8 = "`loc_x'"

* Age (years)
summ agey if nonmiss == 1 & study == 3 [aw = c_weight], d
local loc_x = string(r(p50),"%9.1f") + " (" + string(r(p25),"%9.1f") + " - " + string(r(p75),"%9.1f") + ")"
putexcel B10 = "`loc_x'"

* Female
svy, subpop(if nonmiss == 1 & study == 3): proportion gender, percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel B11 = "`loc_x'"

* Education
svy, subpop(if nonmiss == 1 & study == 3): proportion educ, percent
forvalues n = 1/3 {
	local row = `n' + 12
	local loc_x = string(r(table)[1,`n'],"%9.1f") + " (" + string(r(table)[5,`n'],"%9.1f") + " - " + string(r(table)[6,`n'],"%9.1f") + ")"
	putexcel B`row' = "`loc_x'"
}

* Current smoker
svy, subpop(if nonmiss == 1 & study == 3): proportion smoker, percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel B16 = "`loc_x'"

* BMI
svy, subpop(if nonmiss == 1 & study == 3): proportion bmicat, percent
forvalues n = 1/4 {
	local row = `n' + 17
	local loc_x = string(r(table)[1,`n'],"%9.1f") + " (" + string(r(table)[5,`n'],"%9.1f") + " - " + string(r(table)[6,`n'],"%9.1f") + ")"
	putexcel B`row' = "`loc_x'"
}

* Total diabetes
svy, subpop(if nonmiss == 1 & study == 3): proportion totdiab, stdize(agevar) stdweight(ageweightvar) percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel B22 = "`loc_x'"

* Diagnosed among all with diabetes
svy, subpop(if nonmiss == 1 & study == 3): proportion diag_among_diab, stdize(agevar) stdweight(ageweightvar) percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel B23 = "`loc_x'"

** Third column (ELSA) *********************************************************
* Years of data collection
putexcel C5 = "2012 to 2018"

* Sample size
putexcel C6 = "4,819"

* Deaths
putexcel C7 = "400"

* Follow-up period (years)
putexcel C8 = "5.5 (5.2 - 5.7)"

* Age (years)
putexcel C10 = "64 (57 - 72)"

* Female
putexcel C11 = "50.9 (49.4 - 52.3)"

* Education
putexcel C13 = "30.8 (29.3 - 32.5)"
putexcel C14 = "50.7 (49.0 - 52.4)"
putexcel C15 = "18.4 (17.1 - 19.9)"

* Current smoker
putexcel C16 = "13.7 (12.5 - 15.0)"

* BMI
putexcel C18 = "0.9 (0.6 - 1.3)"
putexcel C19 = "26.4 (25.0 - 28.0)"
putexcel C20 = "41.8 (40.2 - 43.5)"
putexcel C21 = "30.8 (29.3 - 32.4)"

* Total diabetes
putexcel C22 = "11.7 (10.6 - 13.0)"

* Diagnosed among all with diabetes
putexcel C23 = "76.1 (69.3 - 81.7)"

** Fourth column (MHAS) *********************************************************
svyset [pw = m_weight]

* Years of data collection
putexcel D5 = "2012 to 2019"

* Sample size
tab nonmiss if nonmiss == 1 & study == 2, matcell(mat_x)
putexcel D6 = mat_x[1,1], nformat("#,###")

* Deaths
tab censordied if nonmiss == 1 & study == 2, matcell(mat_x)
putexcel D7 = mat_x[2,1], nformat("#,###")

* Follow-up period (years)
summ followtime_y if nonmiss == 1 & study == 2, d
local loc_x = string(r(p50),"%9.1f") + " (" + string(r(p25),"%9.1f") + " - " + string(r(p75),"%9.1f") + ")"
putexcel D8 = "`loc_x'"

* Age (years)
summ agey if nonmiss == 1 & study == 2 [aw = m_weight], d
local loc_x = string(r(p50),"%9.1f") + " (" + string(r(p25),"%9.1f") + " - " + string(r(p75),"%9.1f") + ")"
putexcel D10 = "`loc_x'"

* Female
svy, subpop(if nonmiss == 1 & study == 2): proportion gender, percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel D11 = "`loc_x'"

* Education
svy, subpop(if nonmiss == 1 & study == 2): proportion educ, percent
forvalues n = 1/3 {
	local row = `n' + 12
	local loc_x = string(r(table)[1,`n'],"%9.1f") + " (" + string(r(table)[5,`n'],"%9.1f") + " - " + string(r(table)[6,`n'],"%9.1f") + ")"
	putexcel D`row' = "`loc_x'"
}

* Current smoker
svy, subpop(if nonmiss == 1 & study == 2): proportion smoker, percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel D16 = "`loc_x'"

* BMI
svy, subpop(if nonmiss == 1 & study == 2): proportion bmicat, percent
forvalues n = 1/4 {
	local row = `n' + 17
	local loc_x = string(r(table)[1,`n'],"%9.1f") + " (" + string(r(table)[5,`n'],"%9.1f") + " - " + string(r(table)[6,`n'],"%9.1f") + ")"
	putexcel D`row' = "`loc_x'"
}

* Total diabetes
svy, subpop(if nonmiss == 1 & study == 2): proportion totdiab, stdize(agevar) stdweight(ageweightvar) percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel D22 = "`loc_x'"

* Diagnosed among all with diabetes
svy, subpop(if nonmiss == 1 & study == 2): proportion diag_among_diab, stdize(agevar) stdweight(ageweightvar) percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel D23 = "`loc_x'"

** Fifth column (HAALSI) *********************************************************
svyset, clear

* Years of data collection
putexcel E5 = "2014 to 2019"

* Sample size
tab nonmiss if nonmiss == 1 & study == 4, matcell(mat_x)
putexcel E6 = mat_x[1,1], nformat("#,###")

* Deaths
tab censordied if nonmiss == 1 & study == 4, matcell(mat_x)
putexcel E7 = mat_x[2,1], nformat("#,###")

* Follow-up period (years)
summ followtime_y if nonmiss == 1 & study == 4, d
local loc_x = string(r(p50),"%9.1f") + " (" + string(r(p25),"%9.1f") + " - " + string(r(p75),"%9.1f") + ")"
putexcel E8 = "`loc_x'"

* Age (years)
summ agey if nonmiss == 1 & study == 4, d
local loc_x = string(r(p50),"%9.1f") + " (" + string(r(p25),"%9.1f") + " - " + string(r(p75),"%9.1f") + ")"
putexcel E10 = "`loc_x'"

* Female
proportion gender if nonmiss == 1 & study == 4, percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel E11 = "`loc_x'"

* Education
proportion educ if nonmiss == 1 & study == 4, percent
forvalues n = 1/3 {
	local row = `n' + 12
	local loc_x = string(r(table)[1,`n'],"%9.1f") + " (" + string(r(table)[5,`n'],"%9.1f") + " - " + string(r(table)[6,`n'],"%9.1f") + ")"
	putexcel E`row' = "`loc_x'"
}

* Current smoker
proportion smoker if nonmiss == 1 & study == 4, percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel E16 = "`loc_x'"

* BMI
proportion bmicat if nonmiss == 1 & study == 4, percent
forvalues n = 1/4 {
	local row = `n' + 17
	local loc_x = string(r(table)[1,`n'],"%9.1f") + " (" + string(r(table)[5,`n'],"%9.1f") + " - " + string(r(table)[6,`n'],"%9.1f") + ")"
	putexcel E`row' = "`loc_x'"
}

* Total diabetes
proportion totdiab if nonmiss == 1 & study == 4, stdize(agevar) stdweight(ageweightvar) percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel E22 = "`loc_x'"

* Diagnosed among all with diabetes
proportion diag_among_diab if nonmiss == 1 & study == 4, stdize(agevar) stdweight(ageweightvar) percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel E23 = "`loc_x'"

** Sixth column (HRS) *********************************************************
svyset h_cluster [pw = h_weight], strata(h_strata)

* Years of data collection
putexcel F5 = "2010 to 2019"

* Sample size
tab nonmiss if nonmiss == 1 & study == 1, matcell(mat_x)
putexcel F6 = mat_x[1,1], nformat("#,###")

* Deaths
tab censordied if nonmiss == 1 & study == 1, matcell(mat_x)
putexcel F7 = mat_x[2,1], nformat("#,###")

* Follow-up period (years)
summ followtime_y if nonmiss == 1 & study == 1, d
local loc_x = string(r(p50),"%9.1f") + " (" + string(r(p25),"%9.1f") + " - " + string(r(p75),"%9.1f") + ")"
putexcel F8 = "`loc_x'"

* Age (years)
summ agey if nonmiss == 1 & study == 1 [aw = h_weight], d
local loc_x = string(r(p50),"%9.1f") + " (" + string(r(p25),"%9.1f") + " - " + string(r(p75),"%9.1f") + ")"
putexcel F10 = "`loc_x'"

* Female
svy, subpop(if nonmiss == 1 & study == 1): proportion gender, percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel F11 = "`loc_x'"

* Education
svy, subpop(if nonmiss == 1 & study == 1): proportion educ, percent
forvalues n = 1/3 {
	local row = `n' + 12
	local loc_x = string(r(table)[1,`n'],"%9.1f") + " (" + string(r(table)[5,`n'],"%9.1f") + " - " + string(r(table)[6,`n'],"%9.1f") + ")"
	putexcel F`row' = "`loc_x'"
}

* Current smoker
svy, subpop(if nonmiss == 1 & study == 1): proportion smoker, percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel F16 = "`loc_x'"

* BMI
svy, subpop(if nonmiss == 1 & study == 1): proportion bmicat, percent
forvalues n = 1/4 {
	local row = `n' + 17
	local loc_x = string(r(table)[1,`n'],"%9.1f") + " (" + string(r(table)[5,`n'],"%9.1f") + " - " + string(r(table)[6,`n'],"%9.1f") + ")"
	putexcel F`row' = "`loc_x'"
}

* Total diabetes
svy, subpop(if nonmiss == 1 & study == 1): proportion totdiab, stdize(agevar) stdweight(ageweightvar) percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel F22 = "`loc_x'"

* Diagnosed among all with diabetes
svy, subpop(if nonmiss == 1 & study == 1): proportion diag_among_diab, stdize(agevar) stdweight(ageweightvar) percent
local loc_x = string(r(table)[1,2],"%9.1f") + " (" + string(r(table)[5,2],"%9.1f") + " - " + string(r(table)[6,2],"%9.1f") + ")"
putexcel F23 = "`loc_x'"


****************************************************************************************************
****************************************************************************************************
* Figure 1 data
****************************************************************************************************
****************************************************************************************************
** Create excel doc
putexcel set "${paper_tables}/Figure1_data.xlsx", replace

** Make table frame
** Variables
putexcel A1 = "study"
putexcel B1 = "age"
putexcel C1 = "pct"
putexcel D1 = "pct_ll"
putexcel E1 = "pct_ul"

** Study
forvalues row = 2/4 {
	putexcel A`row' = "China (CHARLS)"
}
forvalues row = 5/7 {
	putexcel A`row' = "England (ELSA)"
}
forvalues row = 8/10 {
	putexcel A`row' = "Mexico (MHAS)"
}
forvalues row = 11/13 {
	putexcel A`row' = "South Africa (HAALSI)"
}
forvalues row = 14/16 {
	putexcel A`row' = "United States (HRS)"
}

** Age
foreach row in 2 5 8 11 14 {
	putexcel B`row' = "51-59"
}
foreach row in 3 6 9 12 15 {
	putexcel B`row' = "60-69"
}
foreach row in 4 7 10 13 16 {
	putexcel B`row' = "70+"
}

** Pct, pct_ll, pct_ul
* CHARLS
svyset [pw = c_weight]
svy, subpop(if nonmiss == 1 & study == 3): proportion totdiab, over(agecat) percent
forvalues n = 4/6 {
	local row = `n' - 2
	local loc_x = round(r(table)[1,`n'],0.1)
	putexcel C`row' = `loc_x'
	local loc_ll = round(r(table)[5,`n'],0.1)
	putexcel D`row' = `loc_ll'
	local loc_ul = round(r(table)[6,`n'],0.1)
	putexcel E`row' = `loc_ul'
}

* ELSA
* 51 - 59
putexcel C5 = "7.9"
putexcel D5 = "6.1"
putexcel E5 = "10.3"
* 50 - 69
putexcel C6 = "12.3"
putexcel D6 = "10.7"
putexcel E6 = "14.0"
* 70+
putexcel C7 = "19.1"
putexcel D7 = "17.0"
putexcel E7 = "21.4"

* MHAS
svyset [pw = m_weight]
svy, subpop(if nonmiss == 1 & study == 2): proportion totdiab, over(agecat) percent
forvalues n = 4/6 {
	local row = `n' + 4
	local loc_x = round(r(table)[1,`n'],0.1)
	putexcel C`row' = `loc_x'
	local loc_ll = round(r(table)[5,`n'],0.1)
	putexcel D`row' = `loc_ll'
	local loc_ul = round(r(table)[6,`n'],0.1)
	putexcel E`row' = `loc_ul'
}

* HAALSI
svyset, clear
proportion totdiab if nonmiss == 1 & study == 4, over(agecat) percent
forvalues n = 4/6 {
	local row = `n' + 7
	local loc_x = round(r(table)[1,`n'],0.1)
	putexcel C`row' = `loc_x'
	local loc_ll = round(r(table)[5,`n'],0.1)
	putexcel D`row' = `loc_ll'
	local loc_ul = round(r(table)[6,`n'],0.1)
	putexcel E`row' = `loc_ul'
}

* HRS
svyset h_cluster [pw = h_weight], strata(h_strata)
svy, subpop(if nonmiss == 1 & study == 1): proportion totdiab, over(agecat) percent
forvalues n = 4/6 {
	local row = `n' + 10
	local loc_x = round(r(table)[1,`n'],0.1)
	putexcel C`row' = `loc_x'
	local loc_ll = round(r(table)[5,`n'],0.1)
	putexcel D`row' = `loc_ll'
	local loc_ul = round(r(table)[6,`n'],0.1)
	putexcel E`row' = `loc_ul'
}


****************************************************************************************************
****************************************************************************************************
* Figure 2 data
****************************************************************************************************
****************************************************************************************************
** Create excel doc
putexcel set "${paper_tables}/Figure2_data.xlsx", replace

** Make table frame
** Variables
putexcel A1 = "study"
putexcel B1 = "diabetes"
putexcel C1 = "rate"
putexcel D1 = "rate_ll"
putexcel E1 = "rate_ul"

** Study
forvalues row = 2/3 {
	putexcel A`row' = "China (CHARLS)"
}
forvalues row = 4/5 {
	putexcel A`row' = "England (ELSA)"
}
forvalues row = 6/7 {
	putexcel A`row' = "Mexico (MHAS)"
}
forvalues row = 8/9 {
	putexcel A`row' = "South Africa (HAALSI)"
}
forvalues row = 10/11 {
	putexcel A`row' = "United States (HRS)"
}

** With/without diabetes
foreach row in 2 4 6 8 10 {
	putexcel B`row' = "Without diabetes"
}
foreach row in 3 5 7 9 11 {
	putexcel B`row' = "Diabetes"
}

** Rate, rate_ll, rate_ul
* CHARLS
svyset [pw = c_weight]
svy, subpop(if study == 3 & nonmiss == 1): poisson censordied i.totdiab i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
margins i.totdiab, subpop(if study == 3 & nonmiss == 1) predict(ir) vce(unconditional) base
forvalues n = 1/2 {
	local row = `n' + 1
	local loc_x = (r(table)[1,`n']*1000)
	putexcel C`row' = `loc_x'
	local loc_ll = (r(table)[5,`n']*1000)
  putexcel D`row' = `loc_ll'
  local loc_ul = (r(table)[6,`n']*1000)
  putexcel E`row' = `loc_ul'
}

* ELSA
* Without diabetes
putexcel C4 = "16.9923137113577"
putexcel D4 = "14.644134925985"
putexcel E4 = "19.3404924967303"
* Diabetes
putexcel C5 = "28.8466119291198"
putexcel D5 = "22.0600198572182"
putexcel E5 = "35.6332040010214"

* MHAS
svyset [pw = m_weight]
svy, subpop(if study == 2 & nonmiss == 1): poisson censordied i.totdiab i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
margins i.totdiab, subpop(if study == 2 & nonmiss == 1) predict(ir) vce(unconditional) base
forvalues n = 1/2 {
	local row = `n' + 5
	local loc_x = (r(table)[1,`n']*1000)
	putexcel C`row' = `loc_x'
	local loc_ll = (r(table)[5,`n']*1000)
  putexcel D`row' = `loc_ll'
  local loc_ul = (r(table)[6,`n']*1000)
  putexcel E`row' = `loc_ul'
}

* HAALSI
svyset, clear
poisson censordied i.totdiab i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if study == 4 & nonmiss == 1, base irr exposure(followtime_y) vce(robust) 
margins i.totdiab, predict(ir) base
forvalues n = 1/2 {
	local row = `n' + 7
	local loc_x = (r(table)[1,`n']*1000)
	putexcel C`row' = `loc_x'
	local loc_ll = (r(table)[5,`n']*1000)
  putexcel D`row' = `loc_ll'
  local loc_ul = (r(table)[6,`n']*1000)
  putexcel E`row' = `loc_ul'
}

* HRS
svyset h_cluster [pw = h_weight], strata(h_strata)
svy, subpop(if study == 1 & nonmiss == 1): poisson censordied i.totdiab i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
margins i.totdiab, subpop(if study == 1 & nonmiss == 1) predict(ir) vce(unconditional) base
forvalues n = 1/2 {
	local row = `n' + 9
	local loc_x = (r(table)[1,`n']*1000)
	putexcel C`row' = `loc_x'
	local loc_ll = (r(table)[5,`n']*1000)
  putexcel D`row' = `loc_ll'
  local loc_ul = (r(table)[6,`n']*1000)
  putexcel E`row' = `loc_ul'
}


****************************************************************************************************
****************************************************************************************************
* Figure 3, Panel A data - overall and men vs. women
****************************************************************************************************
****************************************************************************************************
* Create excel doc
putexcel set "${paper_tables}/Figure3a_data.xlsx", replace


* Make table frame
* Variables
putexcel A1 = "label"
putexcel B1 = "mrr_coef"
putexcel C1 = "mrr_lower"
putexcel D1 = "mrr_upper"
putexcel E1 = "mrr"
putexcel F1 = "mrd_coef"
putexcel G1 = "mrd_lower"
putexcel H1 = "mrd_upper"
putexcel I1 = "mrd"

* label
putexcel A2 = "China (CHARLS)"
putexcel A7 = "England (ELSA)"
putexcel A12 = "Mexico (MHAS)"
putexcel A17 = "South Africa (HAALSI)"
putexcel A22 = "United States (HRS)"

foreach row in 3 8 13 18 23 {
	putexcel A`row' = "Women"
}

foreach row in 4 9 14 19 24 {
	putexcel A`row' = "Men"
}

foreach row in 5 10 15 20 25 {
	putexcel A`row' = "Overall"
}

*** coef, lower, upper
** CHARLS
svyset [pw = c_weight]

* Women
* MRR
svy, subpop(if nonmiss == 1 & study == 3 & gender == 2): poisson censordied i.totdiab i.agecat i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B3 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C3 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D3 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E3 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 3 & gender == 2) dydx(i.totdiab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F3 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G3 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H3 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I3 = "`loc_est'"

* Men
* MRR
svy, subpop(if nonmiss == 1 & study == 3 & gender == 1): poisson censordied i.totdiab i.agecat i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B4 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C4 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D4 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E4 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 3 & gender == 1) dydx(i.totdiab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F4 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G4 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H4 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I4 = "`loc_est'"

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 3): poisson censordied i.totdiab i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B5 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C5 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D5 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E5 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 3) dydx(i.totdiab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F5 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G5 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H5 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I5 = "`loc_est'"

** ELSA
* Women
* MRR
putexcel B8 = "1.88009855520147"
putexcel C8 = "1.2026380644433"
putexcel D8 = "2.93918069099776"
putexcel E8 = "1.88 (1.20 - 2.94)"

* MRD
putexcel F8 = "12.669325611365"
putexcel G8 = "1.58888573774749"
putexcel H8 = "23.7497654849825"
putexcel I8 = "12.7 (1.6 - 23.7)"

* Men
* MRR
putexcel B9 = "1.54169204237826"
putexcel C9 = "1.10698352132208"
putexcel D9 = "2.14710906508689"
putexcel E9 = "1.54 (1.11 - 2.15)"

* MRD
putexcel F9 = "10.8232167567196"
putexcel G9 = "1.47665566845706"
putexcel H9 = "20.1697778449821"
putexcel I9 = "10.8 (1.5 - 20.2)"

* Overall
* MRR
putexcel B10 = "1.69762708122783"
putexcel C10 = "1.30097801526444"
putexcel D10 = "2.21520861467619"
putexcel E10 = "1.70 (1.30 - 2.22)"

* MRD
putexcel F10 = "11.8542982177621"
putexcel G10 = "4.79788329315435"
putexcel H10 = "18.9107131423699"
putexcel I10 = "11.9 (4.8 - 18.9)"

** MHAS
svyset [pw = m_weight]

* Women
* MRR
svy, subpop(if nonmiss == 1 & study == 2 & gender == 2): poisson censordied i.totdiab i.agecat i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B13 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C13 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D13 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E13 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 2 & gender == 2) dydx(i.totdiab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F13 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G13 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H13 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I13 = "`loc_est'"

* Men
* MRR
svy, subpop(if nonmiss == 1 & study == 2 & gender == 1): poisson censordied i.totdiab i.agecat i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B14 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C14 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D14 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E14 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 2 & gender == 1) dydx(i.totdiab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F14 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G14 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H14 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I14 = "`loc_est'"

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 2): poisson censordied i.totdiab i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B15 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C15 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D15 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E15 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 2) dydx(i.totdiab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F15 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G15 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H15 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I15 = "`loc_est'"

** HAALSI
svyset, clear

* Women
* MRR
poisson censordied i.totdiab i.agecat i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4 & gender == 2, base irr exposure(followtime_y) vce(robust)
local loc_x = r(table)[1,2]
putexcel B18 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C18 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D18 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E18 = "`loc_est'"

* MRD
margins, dydx(i.totdiab) predict(ir) base
local loc_x = (r(table)[1,2]*1000)
putexcel F18 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G18 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H18 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I18 = "`loc_est'"

* Men
* MRR
poisson censordied i.totdiab i.agecat i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4 & gender == 1, base irr exposure(followtime_y) vce(robust)
local loc_x = r(table)[1,2]
putexcel B19 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C19 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D19 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E19 = "`loc_est'"

* MRD
margins, dydx(i.totdiab) predict(ir) base
local loc_x = (r(table)[1,2]*1000)
putexcel F19 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G19 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H19 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I19 = "`loc_est'"

* Overall
* MRR
poisson censordied i.totdiab i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4, base irr exposure(followtime_y) vce(robust)
local loc_x = r(table)[1,2]
putexcel B20 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C20 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D20 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E20 = "`loc_est'"

* MRD
margins, dydx(i.totdiab) predict(ir) base
local loc_x = (r(table)[1,2]*1000)
putexcel F20 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G20 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H20 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I20 = "`loc_est'"

** HRS
svyset h_cluster [pw = h_weight], strata(h_strata)

* Women
* MRR
svy, subpop(if nonmiss == 1 & study == 1 & gender == 2): poisson censordied i.totdiab i.agecat i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B23 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C23 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D23 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E23 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 1 & gender == 2) dydx(i.totdiab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F23 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G23 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H23 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I23 = "`loc_est'"

* Men
* MRR
svy, subpop(if nonmiss == 1 & study == 1 & gender == 1): poisson censordied i.totdiab i.agecat i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B24 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C24 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D24 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E24 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 1 & gender == 1) dydx(i.totdiab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F24 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G24 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H24 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I24 = "`loc_est'"

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 1): poisson censordied i.totdiab i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B25 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C25 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D25 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E25 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 1) dydx(i.totdiab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F25 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G25 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H25 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I25 = "`loc_est'"


****************************************************************************************************
****************************************************************************************************
* Figure 3, Panel B data - diagnosed vs. no diabetes and undiagnosed vs. no diabetes
****************************************************************************************************
****************************************************************************************************
* Create excel doc
putexcel set "${paper_tables}/Figure3b_data.xlsx", replace

* Make table frame
* Variables
putexcel A1 = "label"
putexcel B1 = "mrr_coef"
putexcel C1 = "mrr_lower"
putexcel D1 = "mrr_upper"
putexcel E1 = "mrr"
putexcel F1 = "mrd_coef"
putexcel G1 = "mrd_lower"
putexcel H1 = "mrd_upper"
putexcel I1 = "mrd"

* label
putexcel A2 = "China (CHARLS)"
putexcel A6 = "England (ELSA)"
putexcel A10 = "Mexico (MHAS)"
putexcel A14 = "South Africa (HAALSI)"
putexcel A18 = "United States (HRS)"

foreach row in 3 7 11 15 19 {
	putexcel A`row' = "Diagnosed vs. no diabetes"
}

foreach row in 4 8 12 16 20 {
	putexcel A`row' = "Undiagnosed vs. no diabetes"
}

*** coef, lower, upper
** CHARLS
svyset [pw = c_weight]

* Diagnosed vs. no diabetes
* MRR
svy, subpop(if nonmiss == 1 & study == 3 & inlist(diab3,0,2)): poisson censordied i.diab3 i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B3 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C3 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D3 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E3 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 3 & inlist(diab3,0,2)) dydx(i.diab3) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F3 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G3 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H3 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I3 = "`loc_est'"

* Undiagnosed vs. no diabetes
* MRR
svy, subpop(if nonmiss == 1 & study == 3 & inlist(diab3,0,1)): poisson censordied i.diab3 i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B4 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C4 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D4 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E4 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 3 & inlist(diab3,0,1)) dydx(i.diab3) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F4 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G4 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H4 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I4 = "`loc_est'"

** ELSA
* Diagnosed vs. no diabetes
* MRR
putexcel B7 = "1.85965438749481"
putexcel C7 = "1.39526984457749"
putexcel D7 = "2.47859899959061"
putexcel E7 = "1.86 (1.40 - 2.48)"

* MRD
putexcel F7 = "14.4021512950314"
putexcel G7 = "6.11684990619038"
putexcel H7 = "22.6874526838724"
putexcel I7 = "14.4 (6.1 - 22.7)"

* Uniagnosed vs. no diabetes
* MRR
putexcel B8 = "1.27584675431268"
putexcel C8 = "0.768838950368545"
putexcel D8 = "2.11719884861441"
putexcel E8 = "1.28 (0.77 - 2.12)"

* MRD
putexcel F8 = "4.55215093244502"
putexcel G8 = "-5.98736699490149"
putexcel H8 = "15.0916688597915"
putexcel I8 = "4.6 (-6.0 - 15.1)"

** MHAS
svyset [pw = m_weight]

* Diagnosed vs. no diabetes
* MRR
svy, subpop(if nonmiss == 1 & study == 2 & inlist(diab3,0,2)): poisson censordied i.diab3 i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B11 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C11 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D11 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E11 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 2 & inlist(diab3,0,2)) dydx(i.diab3) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F11 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G11 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H11 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I11 = "`loc_est'"

* Undiagnosed vs. no diabetes
* MRR
svy, subpop(if nonmiss == 1 & study == 2 & inlist(diab3,0,1)): poisson censordied i.diab3 i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B12 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C12 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D12 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E12 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 2 & inlist(diab3,0,1)) dydx(i.diab3) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F12 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G12 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H12 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I12 = "`loc_est'"

** HAALSI
svyset, clear

* Diagnosed vs. no diabetes
* MRR
poisson censordied i.diab3 i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4 & inlist(diab3,0,2), base irr exposure(followtime_y) vce(robust)
local loc_x = r(table)[1,2]
putexcel B15 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C15 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D15 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E15 = "`loc_est'"

* MRD
margins, dydx(i.diab3) predict(ir) base
local loc_x = (r(table)[1,2]*1000)
putexcel F15 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G15 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H15 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I15 = "`loc_est'"

* Undiagnosed vs. no diabetes
* MRR
poisson censordied i.diab3 i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4 & inlist(diab3,0,1), base irr exposure(followtime_y) vce(robust)
local loc_x = r(table)[1,2]
putexcel B16 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C16 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D16 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E16 = "`loc_est'"

* MRD
margins, dydx(i.diab3) predict(ir) base
local loc_x = (r(table)[1,2]*1000)
putexcel F16 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G16 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H16 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I16 = "`loc_est'"

** HRS
svyset h_cluster [pw = h_weight], strata(h_strata)

* Diagnosed vs. no diabetes
* MRR
svy, subpop(if nonmiss == 1 & study == 1 & inlist(diab3,0,2)): poisson censordied i.diab3 i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B19 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C19 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D19 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E19 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 1 & inlist(diab3,0,2)) dydx(i.diab3) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F19 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G19 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H19 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I19 = "`loc_est'"

* Undiagnosed vs. no diabetes
* MRR
svy, subpop(if nonmiss == 1 & study == 1 & inlist(diab3,0,1)): poisson censordied i.diab3 i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B20 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C20 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D20 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E20 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 1 & inlist(diab3,0,1)) dydx(i.diab3) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F20 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G20 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H20 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I20 = "`loc_est'"


****************************************************************************************************
****************************************************************************************************
* Appendix 6 - Adjusted all-cause mortality rates by cohort
****************************************************************************************************
****************************************************************************************************
** Create excel doc
putexcel set "${paper_tables}/HRS diabetes mortality analysis ${date}.xlsx", sheet("Appendix 6") modify

* Table formatting
* Lines
putexcel A3:F3, border(top)
putexcel A3:F3, border(bottom)
putexcel A6:F6, border(bottom)

* Font
putexcel A1:F23, font("Arial",10)

* Column width
mata: b = xl()
mata: b.load_book("${paper_tables}/HRS diabetes mortality analysis ${date}.xlsx")
mata: b.set_sheet("Appendix 6")
mata: b.set_column_width(1,1,25)
mata: b.set_column_width(2,6,20)

* Center justify
putexcel B3:F6, hcenter

* Right justify
putexcel A5:A6, right

* Make table frame
* Title
putexcel A1 = "Appendix 6"

* Top rows
putexcel B3 = "China (CHARLS)"
putexcel C3 = "England (ELSA)"
putexcel D3 = "Mexico (MHAS)"
putexcel E3 = "South Africa (HAALSI)"
putexcel F3 = "United States (HRS)"

* First column *************************************************************************************
putexcel A4 = "Mortality rate"
putexcel A5 = "Without diabetes"
putexcel A6 = "Diabetes"

** Fill in results
** Second column (CHARLS) *********************************************************
svyset [pw = c_weight]
svy, subpop(if study == 3 & nonmiss == 1): poisson censordied i.totdiab i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
margins i.totdiab, subpop(if study == 3 & nonmiss == 1) predict(ir) vce(unconditional) base
forvalues n = 1/2 {
	local row = `n' + 4
	local loc_x = (r(table)[1,`n']*1000)
	local loc_ll = (r(table)[5,`n']*1000)
	local loc_ul = (r(table)[6,`n']*1000)
	local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
	putexcel B`row' = "`loc_est'"
}

** Third column (ELSA) *********************************************************
putexcel C5 = "17.0 (14.6 - 19.3)"
putexcel C6 = "28.8 (22.1 - 35.6)"

** Fourth column (MHAS) *********************************************************
svyset [pw = m_weight]
svy, subpop(if study == 2 & nonmiss == 1): poisson censordied i.totdiab i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
margins i.totdiab, subpop(if study == 2 & nonmiss == 1) predict(ir) vce(unconditional) base
forvalues n = 1/2 {
	local row = `n' + 4
	local loc_x = (r(table)[1,`n']*1000)
	local loc_ll = (r(table)[5,`n']*1000)
	local loc_ul = (r(table)[6,`n']*1000)
	local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
	putexcel D`row' = "`loc_est'"
}

** Fifth column (HAALSI)
svyset, clear
poisson censordied i.totdiab i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if study == 4 & nonmiss == 1, base irr exposure(followtime_y) vce(robust) 
margins i.totdiab, predict(ir) base
forvalues n = 1/2 {
	local row = `n' + 4
	local loc_x = (r(table)[1,`n']*1000)
	local loc_ll = (r(table)[5,`n']*1000)
	local loc_ul = (r(table)[6,`n']*1000)
	local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
	putexcel E`row' = "`loc_est'"
}

** Sixth column (HRS)
svyset h_cluster [pw = h_weight], strata(h_strata)
svy, subpop(if study == 1 & nonmiss == 1): poisson censordied i.totdiab i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
margins i.totdiab, subpop(if study == 1 & nonmiss == 1) predict(ir) vce(unconditional) base
forvalues n = 1/2 {
	local row = `n' + 4
	local loc_x = (r(table)[1,`n']*1000)
	local loc_ll = (r(table)[5,`n']*1000)
	local loc_ul = (r(table)[6,`n']*1000)
	local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
	putexcel F`row' = "`loc_est'"
}


****************************************************************************************************
****************************************************************************************************
* Appendix 7 data - Mortality rate ratios by age groups
****************************************************************************************************
****************************************************************************************************
* Create excel doc
putexcel set "${paper_tables}/Appendix7_data.xlsx", replace

* Make table frame
* Variables
putexcel A1 = "label"
putexcel B1 = "mrr_coef"
putexcel C1 = "mrr_lower"
putexcel D1 = "mrr_upper"
putexcel E1 = "mrr"

* label
putexcel A2 = "China (CHARLS)"
putexcel A7 = "England (ELSA)"
putexcel A12 = "Mexico (MHAS)"
putexcel A17 = "South Africa (HAALSI)"
putexcel A22 = "United States (HRS)"

foreach row in 3 8 13 18 23 {
	putexcel A`row' = "51-59"
}

foreach row in 4 9 14 19 24 {
	putexcel A`row' = "60-69"
}

foreach row in 5 10 15 20 25 {
	putexcel A`row' = "70+"
}

*** coef, lower, upper
** CHARLS
svyset [pw = c_weight]

* 51-59
* MRR
svy, subpop(if nonmiss == 1 & study == 3 & agecat == 1): poisson censordied i.totdiab i.gender i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B3 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C3 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D3 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E3 = "`loc_est'"

* 60-69
* MRR
svy, subpop(if nonmiss == 1 & study == 3 & agecat == 2): poisson censordied i.totdiab i.gender i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B4 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C4 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D4 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E4 = "`loc_est'"

* 70+
* MRR
svy, subpop(if nonmiss == 1 & study == 3 & agecat == 3): poisson censordied i.totdiab i.gender i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B5 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C5 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D5 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E5 = "`loc_est'"

** ELSA
* 51-59
* MRR
putexcel B8 = "4.51418129816385"
putexcel C8 = "1.35282215405753"
putexcel D8 = "15.0632015683458"
putexcel E8 = "4.51 (1.35 - 15.06)"

* 60-69
* MRR
putexcel B9 = "1.890332"
putexcel C9 = "1.092246"
putexcel D9 = "3.271568"
putexcel E9 = "1.89 (1.09 - 3.27)"

* 70+
* MRR
putexcel B10 = "1.58914617786094"
putexcel C10 = "1.18844489705265"
putexcel D10 = "2.12494965553134"
putexcel E10 = "1.59 (1.19 - 2.12)"

** MHAS
svyset [pw = m_weight]

* 51-59
* MRR
svy, subpop(if nonmiss == 1 & study == 2 & agecat == 1): poisson censordied i.totdiab i.gender i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B13 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C13 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D13 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E13 = "`loc_est'"

* 60-69
* MRR
svy, subpop(if nonmiss == 1 & study == 2 & agecat == 2): poisson censordied i.totdiab i.gender i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B14 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C14 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D14 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E14 = "`loc_est'"

* 70+
* MRR
svy, subpop(if nonmiss == 1 & study == 2 & agecat == 3): poisson censordied i.totdiab i.gender i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B15 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C15 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D15 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E15 = "`loc_est'"

** HAALSI
svyset, clear

* 51-59
* MRR
poisson censordied i.totdiab i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4 & agecat == 1, base irr exposure(followtime_y) vce(robust)
local loc_x = r(table)[1,2]
putexcel B18 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C18 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D18 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E18 = "`loc_est'"

* 60-69
* MRR
poisson censordied i.totdiab i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4 & agecat == 2, base irr exposure(followtime_y) vce(robust)
local loc_x = r(table)[1,2]
putexcel B19 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C19 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D19 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E19 = "`loc_est'"

* 70+
* MRR
poisson censordied i.totdiab i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4 & agecat == 3, base irr exposure(followtime_y) vce(robust)
local loc_x = r(table)[1,2]
putexcel B20 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C20 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D20 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E20 = "`loc_est'"

** HRS
svyset h_cluster [pw = h_weight], strata(h_strata)

* 51-59
* MRR
svy, subpop(if nonmiss == 1 & study == 1 & agecat == 1): poisson censordied i.totdiab i.gender i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B23 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C23 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D23 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E23 = "`loc_est'"

* 60-69
* MRR
svy, subpop(if nonmiss == 1 & study == 1 & agecat == 2): poisson censordied i.totdiab i.gender i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B24 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C24 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D24 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E24 = "`loc_est'"

* 70+
* MRR
svy, subpop(if nonmiss == 1 & study == 1 & agecat == 3): poisson censordied i.totdiab i.gender i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B25 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C25 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D25 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E25 = "`loc_est'"


****************************************************************************************************
****************************************************************************************************
* Appendix 8 data - comparisons of diagnosed vs. undiagnosed
****************************************************************************************************
****************************************************************************************************
* Create excel doc
putexcel set "${paper_tables}/Appendix8_data.xlsx", replace

* Make table frame
* Variables
putexcel A1 = "label"
putexcel B1 = "mrr_coef"
putexcel C1 = "mrr_lower"
putexcel D1 = "mrr_upper"
putexcel E1 = "mrr"
putexcel F1 = "mrd_coef"
putexcel G1 = "mrd_lower"
putexcel H1 = "mrd_upper"
putexcel I1 = "mrd"

* label
putexcel A2 = "China (CHARLS)"
putexcel A4 = "England (ELSA)"
putexcel A6 = "Mexico (MHAS)"
putexcel A8 = "South Africa (HAALSI)"
putexcel A10 = "United States (HRS)"

*** coef, lower, upper, estimate
** CHARLS
svyset [pw = c_weight]

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 3): poisson censordied i.diag_among_diab i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B2 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C2 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D2 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E2 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 3) dydx(i.diag_among_diab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F2 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G2 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H2 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I2 = "`loc_est'"

** ELSA
* Overall
* MRR
putexcel B4 = "1.48522475952351"
putexcel C4 = "0.838640305274978"
putexcel D4 = "2.63032026057749"
putexcel E4 = "1.49 (0.84 - 2.63)"

* MRD
putexcel F4 = "13.4436159987399"
putexcel G4 = "-4.15960612813728"
putexcel H4 = "31.0468381256171"
putexcel I4 = "13.4 (-4.2 - 31.0)"

** MHAS
svyset [pw = m_weight]

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 2): poisson censordied i.diag_among_diab i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B6 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C6 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D6 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E6 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 2) dydx(i.diag_among_diab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F6 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G6 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H6 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I6 = "`loc_est'"

** HAALSI
svyset, clear

* Overall
* MRR
poisson censordied i.diag_among_diab i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4, base irr exposure(followtime_y) vce(robust)
local loc_x = r(table)[1,2]
putexcel B8 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C8 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D8 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E8 = "`loc_est'"

* MRD
margins, dydx(i.diag_among_diab) predict(ir) base
local loc_x = (r(table)[1,2]*1000)
putexcel F8 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G8 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H8 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I8 = "`loc_est'"

** HRS
svyset h_cluster [pw = h_weight], strata(h_strata)

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 1): poisson censordied i.diag_among_diab i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B10 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C10 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D10 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E10 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 1) dydx(i.diag_among_diab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F10 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G10 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H10 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I10 = "`loc_est'"


****************************************************************************************************
****************************************************************************************************
* Appendix 9 data - sensitivity analysis using Cox proportional hazards models instead of Poisson
*                   with an offset for log-transformed person-years and robust standard errors
****************************************************************************************************
****************************************************************************************************
* Create excel doc
putexcel set "${paper_tables}/Appendix9_data.xlsx", replace

* Make table frame
* Variables
putexcel A1 = "label"
putexcel B1 = "poisson_mrr"
putexcel C1 = "cox_hr"
putexcel D1 = "gompertz_hr"
putexcel E1 = "exp_hr"

* label
putexcel A2 = "China (CHARLS)"
putexcel A3 = "England (ELSA)"
putexcel A4 = "Mexico (MHAS)"
putexcel A5 = "South Africa (HAALSI)"
putexcel A6 = "United States (HRS)"

* Generate ages at entry and exit for age-on-study as the time scale in survival analyses
capture drop age_entry age_exit
gen age_entry = agey
gen age_exit  = agey + followtime_y

** CHARLS ----------------------------------------------------------------------
svyset [pw = c_weight]

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 3): poisson censordied i.totdiab i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel B2 = "`loc_est'"

* Cox HR
preserve

	stset age_exit [pw = c_weight] if nonmiss == 1 & study == 3, failure(censordied) enter(age_entry)
	svy, subpop(if nonmiss == 1 & study == 3): stcox i.totdiab i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, base

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel C2 = "`loc_est'"
	
restore

* Gompertz HR
preserve

	stset age_exit [pw = c_weight] if nonmiss == 1 & study == 3, failure(censordied) enter(age_entry)
	svy, subpop(if study == 3 & nonmiss == 1): ///
		streg i.totdiab i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, dist(gompertz) base

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel D2 = "`loc_est'"
	
restore

* Exponential HR
preserve

	stset age_exit [pw = c_weight] if nonmiss == 1 & study == 3, failure(censordied) enter(age_entry)
	svy, subpop(if study == 3 & nonmiss == 1): ///
		streg i.totdiab i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, dist(exp) base

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel E2 = "`loc_est'"
	
restore


** ELSA ------------------------------------------------------------------------
* Overall
* MRR
putexcel B3 = "TBD"

* Cox HR
putexcel C3 = "TBD"

* Gompertz HR
putexcel D3 = "TBD"

* Exponential HR
putexcel E3 = "TBD"

** MHAS ------------------------------------------------------------------------
svyset [pw = m_weight]

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 2): poisson censordied i.totdiab i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	putexcel D2 = `loc_ul'
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel B4 = "`loc_est'"

* Cox HR
preserve

	stset age_exit [pw = m_weight] if nonmiss == 1 & study == 2, failure(censordied) enter(age_entry)
	svy, subpop(if nonmiss == 1 & study == 2): stcox i.totdiab i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, base

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel C4 = "`loc_est'"
	
restore

* Gompertz HR
preserve

	stset age_exit [pw = m_weight] if nonmiss == 1 & study == 2, failure(censordied) enter(age_entry)
	svy, subpop(if study == 2 & nonmiss == 1): ///
		streg i.totdiab i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, dist(gompertz) base

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel D4 = "`loc_est'"
	
restore

* Exponential HR
preserve

	stset age_exit [pw = m_weight] if nonmiss == 1 & study == 2, failure(censordied) enter(age_entry)
	svy, subpop(if study == 2 & nonmiss == 1): ///
		streg i.totdiab i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, dist(exp) base

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel E4 = "`loc_est'"
	
restore

** HAALSI ----------------------------------------------------------------------
svyset, clear

* Overall
* MRR
poisson censordied i.totdiab i.agecat i.gender i.educ i.i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4, base irr exposure(followtime_y) vce(robust) 

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	putexcel D2 = `loc_ul'
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel B5 = "`loc_est'"

* Cox HR
preserve

	stset age_exit if nonmiss == 1 & study == 4, failure(censordied) enter(age_entry)
	stcox i.totdiab i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4, base

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel C5 = "`loc_est'"
	
restore

* Gompertz HR
preserve

	stset age_exit if nonmiss == 1 & study == 4, failure(censordied) enter(age_entry)
	streg i.totdiab i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4, dist(gompertz) base

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel D5 = "`loc_est'"
	
restore

* Exponential HR
preserve

	stset age_exit if nonmiss == 1 & study == 4, failure(censordied) enter(age_entry)
	streg i.totdiab i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4, dist(exp) base

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel E5 = "`loc_est'"
	
restore

** HRS ----------------------------------------------------------------------
svyset h_cluster [pw = h_weight], strata(h_strata)

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 1): poisson censordied i.totdiab i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	putexcel D2 = `loc_ul'
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel B6 = "`loc_est'"

* Cox HR
preserve

	stset age_exit [pw = h_weight] if nonmiss == 1 & study == 1, failure(censordied) enter(age_entry)
	svy, subpop(if nonmiss == 1 & study == 1): stcox i.totdiab i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, base

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel C6 = "`loc_est'"
	
restore

* Gompertz HR
preserve

	stset age_exit [pw = h_weight] if nonmiss == 1 & study == 1, failure(censordied) enter(age_entry)
	svy, subpop(if study == 1 & nonmiss == 1): ///
		streg i.totdiab i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, dist(gompertz) base

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel D6 = "`loc_est'"
	
restore

* Exponential HR
preserve

	stset age_exit [pw = h_weight] if nonmiss == 1 & study == 1, failure(censordied) enter(age_entry)
	svy, subpop(if study == 1 & nonmiss == 1): ///
		streg i.totdiab i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, dist(exp) base

	local loc_x = r(table)[1,2]
	local loc_ll = r(table)[5,2]
	local loc_ul = r(table)[6,2]
	local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
	putexcel E6 = "`loc_est'"
	
restore

****************************************************************************************************
****************************************************************************************************
* Appendix 10 data - sensitivity analysis using self-report of diabetes medication instead of
*                    self-report of diabetes diagnosis
****************************************************************************************************
****************************************************************************************************
* Create excel doc
putexcel set "${paper_tables}/Appendix10_data.xlsx", replace

* Make table frame
* Variables
putexcel A1 = "label"
putexcel B1 = "mrr_coef"
putexcel C1 = "mrr_lower"
putexcel D1 = "mrr_upper"
putexcel E1 = "mrr"
putexcel F1 = "mrd_coef"
putexcel G1 = "mrd_lower"
putexcel H1 = "mrd_upper"
putexcel I1 = "mrd"

* label
putexcel A2 = "China (CHARLS)"
putexcel A4 = "England (ELSA)"
putexcel A6 = "Mexico (MHAS)"
putexcel A8 = "South Africa (HAALSI)"
putexcel A10 = "United States (HRS)"

*** coef, lower, upper, estimate
** CHARLS
svyset [pw = c_weight]

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 3): poisson censordied i.totdiab_rx i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B2 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C2 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D2 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E2 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 3) dydx(i.totdiab_rx) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F2 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G2 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H2 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I2 = "`loc_est'"

** ELSA
* Overall
* MRR
putexcel B4 = "1.71916588403095"
putexcel C4 = "1.31337115415399"
putexcel D4 = "2.25033976684202"
putexcel E4 = "1.72 (1.31 - 2.25)"

* MRD
putexcel F4 = "12.2238481457261"
putexcel G4 = "4.98121442241421"
putexcel H4 = "19.4664818690379"
putexcel I4 = "12.2 (5.0 - 19.5)"

** MHAS
svyset [pw = m_weight]

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 2): poisson censordied i.totdiab_rx i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B6 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C6 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D6 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E6 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 2) dydx(i.totdiab_rx) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F6 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G6 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H6 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I6 = "`loc_est'"

** HAALSI
svyset, clear

* Overall
* MRR
poisson censordied i.totdiab_rx i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4, base irr exposure(followtime_y) vce(robust)
local loc_x = r(table)[1,2]
putexcel B8 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C8 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D8 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E8 = "`loc_est'"

* MRD
margins, dydx(i.totdiab_rx) predict(ir) base
local loc_x = (r(table)[1,2]*1000)
putexcel F8 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G8 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H8 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I8 = "`loc_est'"

** HRS
svyset h_cluster [pw = h_weight], strata(h_strata)

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 1): poisson censordied i.totdiab_rx i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B10 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C10 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D10 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E10 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 1) dydx(i.totdiab_rx) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F10 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G10 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H10 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I10 = "`loc_est'"


****************************************************************************************************
****************************************************************************************************
* Appendix 11 data - sensitivity analysis not adjusting for BMI
****************************************************************************************************
****************************************************************************************************
* Create excel doc
putexcel set "${paper_tables}/Appendix11_data.xlsx", replace

* Make table frame
* Variables
putexcel A1 = "label"
putexcel B1 = "mrr_coef"
putexcel C1 = "mrr_lower"
putexcel D1 = "mrr_upper"
putexcel E1 = "mrr"
putexcel F1 = "mrd_coef"
putexcel G1 = "mrd_lower"
putexcel H1 = "mrd_upper"
putexcel I1 = "mrd"

* label
putexcel A2 = "China (CHARLS)"
putexcel A4 = "England (ELSA)"
putexcel A6 = "Mexico (MHAS)"
putexcel A8 = "South Africa (HAALSI)"
putexcel A10 = "United States (HRS)"

*** coef, lower, upper, estimate
** CHARLS
svyset [pw = c_weight]

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 3): poisson censordied i.totdiab i.agecat i.gender i.educ i.c_econtert i.smoker, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B2 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C2 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D2 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E2 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 3) dydx(i.totdiab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F2 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G2 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H2 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I2 = "`loc_est'"

** ELSA
* Overall
* MRR
putexcel B4 = "1.50970649580748"
putexcel C4 = "1.16023306550726"
putexcel D4 = "1.96444470619085"
putexcel E4 = "1.51 (1.16 - 1.96)"

* MRD
putexcel F4 = "8.67062764025615"
putexcel G4 = "2.3965863005179"
putexcel H4 = "14.9446689799944"
putexcel I4 = "8.7 (2.4 - 14.9)"

** MHAS
svyset [pw = m_weight]

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 2): poisson censordied i.totdiab i.agecat i.gender i.educ i.m_econtert i.smoker, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B6 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C6 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D6 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E6 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 2) dydx(i.totdiab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F6 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G6 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H6 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I6 = "`loc_est'"

** HAALSI
svyset, clear

* Overall
* MRR
poisson censordied i.totdiab i.agecat i.gender i.educ i.z_econtert i.smoker i.hiv if nonmiss == 1 & study == 4, base irr exposure(followtime_y) vce(robust)
local loc_x = r(table)[1,2]
putexcel B8 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C8 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D8 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E8 = "`loc_est'"

* MRD
margins, dydx(i.totdiab) predict(ir) base
local loc_x = (r(table)[1,2]*1000)
putexcel F8 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G8 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H8 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I8 = "`loc_est'"

** HRS
svyset h_cluster [pw = h_weight], strata(h_strata)

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 1): poisson censordied i.totdiab i.agecat i.gender i.educ i.h_econtert i.smoker, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B10 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C10 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D10 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E10 = "`loc_est'"

* MRD
margins, subpop(if nonmiss == 1 & study == 1) dydx(i.totdiab) predict(ir) vce(unconditional) base
local loc_x = (r(table)[1,2]*1000)
putexcel F10 = `loc_x'
local loc_ll = (r(table)[5,2]*1000)
putexcel G10 = `loc_ll'
local loc_ul = (r(table)[6,2]*1000)
putexcel H10 = `loc_ul'
local loc_est = string(`loc_x',"%9.1f") + " (" + string(`loc_ll',"%9.1f") + " - " + string(`loc_ul',"%9.1f") + ")"
putexcel I10 = "`loc_est'"


****************************************************************************************************
****************************************************************************************************
* Non-table statistics
****************************************************************************************************
****************************************************************************************************
* Methods
proportion fasting if nonmiss == 1 & study == 3, percent
proportion fasting if nonmiss == 1 & study == 4, percent
proportion hiv if nonmiss == 1 & study == 4, percent

* Results paragraph 1
count if nonmiss == 1
count if nonmiss == 1 & censordied == 1
svyset h_cluster [pw = h_weight], strata(h_strata)
svy, subpop(if nonmiss == 1 & study == 1): proportion educ2, percent

* Total follow-up time (years)
summ followtime_y if nonmiss == 1 & study == 1
disp r(sum)
summ followtime_y if nonmiss == 1 & study == 2
disp r(sum)
summ followtime_y if nonmiss == 1 & study == 3
disp r(sum)
summ followtime_y if nonmiss == 1 & study == 4
disp r(sum)
summ followtime_y if nonmiss == 1
disp r(sum)


****************************************************************************************************
* Close Log
****************************************************************************************************
log close


/*
****************************************************************************************************
****************************************************************************************************
* Appendix 10 data - sensitivity analysis excluding first two years of follow up and using Cox 
*                    proportional hazards models instead of Poisson with an offset for 
*                    log-transformed person-years and robust standard errors
****************************************************************************************************
****************************************************************************************************
* Create excel doc
putexcel set "${paper_tables}/Appendix10_data.xlsx", replace

* Make table frame
* Variables
putexcel A1 = "label"
putexcel B1 = "mrr_coef"
putexcel C1 = "mrr_lower"
putexcel D1 = "mrr_upper"
putexcel E1 = "mrr"
putexcel F1 = "hr_coef"
putexcel G1 = "hr_lower"
putexcel H1 = "hr_upper"
putexcel I1 = "hr"

* label
putexcel A2 = "China (CHARLS)"
putexcel A4 = "England (ELSA)"
putexcel A6 = "Mexico (MHAS)"
putexcel A8 = "South Africa (HAALSI)"
putexcel A10 = "United States (HRS)"

*** coef, lower, upper, estimate
** CHARLS
svyset [pw = c_weight]

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 3 & inrange(followtime_m,25,9999999)): poisson censordied i.totdiab i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B2 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C2 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D2 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E2 = "`loc_est'"

* HR
preserve
stset followtime_y [pw = c_weight] if nonmiss == 1 & study == 3 & inrange(followtime_m,25,9999999), failure(censordied)
svy, subpop(if nonmiss == 1 & study == 3 & inrange(followtime_m,25,9999999)): stcox i.totdiab i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat, base
local loc_x = r(table)[1,2]
putexcel F2 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel G2 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel H2 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel I2 = "`loc_est'"

* Check proportional hazards assumption
* Unadjusted
stphplot, by(totdiab) nolntime plot1opts(msymbol(none)) plot2opts(msymbol(none))
graph export "${paper_figures}/Appendix5_CHARLS_loglog_unadjusted_${date}.png", as(png) replace

* Adjusted
stphplot, by(totdiab) nolntime adjustfor(i.agecat i.gender i.educ i.c_econtert i.smoker b2.bmicat) plot1opts(msymbol(none)) ///
          plot2opts(msymbol(none))
graph export "${paper_figures}/Appendix5_CHARLS_loglog_adjusted_${date}.png", as(png) replace
restore

** ELSA
* Overall
* MRR
putexcel B4 = ""
putexcel C4 = ""
putexcel D4 = ""
putexcel E4 = ""

* HR
putexcel F4 = ""
putexcel G4 = ""
putexcel H4 = ""
putexcel I4 = ""

** MHAS
svyset [pw = m_weight]

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 2 & inrange(followtime_m,25,9999999)): poisson censordied i.totdiab i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B6 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C6 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D6 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E6 = "`loc_est'"

* HR
preserve
stset followtime_y [pw = m_weight] if nonmiss == 1 & study == 2 & inrange(followtime_m,25,9999999), failure(censordied)
svy, subpop(if nonmiss == 1 & study == 2 & inrange(followtime_m,25,9999999)): stcox i.totdiab i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat, base
local loc_x = r(table)[1,2]
putexcel F6 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel G6 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel H6 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel I6 = "`loc_est'"

* Check proportional hazards assumption
* Unadjusted
stphplot, by(totdiab) nolntime plot1opts(msymbol(none)) plot2opts(msymbol(none))
graph export "${paper_figures}/Appendix5_MHAS_loglog_unadjusted_${date}.png", as(png) replace

* Adjusted
stphplot, by(totdiab) nolntime adjustfor(i.agecat i.gender i.educ i.m_econtert i.smoker b2.bmicat) plot1opts(msymbol(none)) ///
          plot2opts(msymbol(none))
graph export "${paper_figures}/Appendix5_MHAS_loglog_adjusted_${date}.png", as(png) replace
restore

** HAALSI
svyset, clear

* Overall
* MRR
poisson censordied i.totdiab_rx i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4 & inrange(followtime_m,25,9999999), base irr exposure(followtime_y) vce(robust)
local loc_x = r(table)[1,2]
putexcel B8 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C8 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D8 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E8 = "`loc_est'"

* HR
preserve
stset followtime_y if nonmiss == 1 & study == 4 & inrange(followtime_m,25,9999999), failure(censordied)
stcox i.totdiab i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat i.hiv if nonmiss == 1 & study == 4 & inrange(followtime_m,25,9999999), base
local loc_x = r(table)[1,2]
putexcel F8 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel G8 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel H8 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel I8 = "`loc_est'"

* Check proportional hazards assumption
* Unadjusted
stphplot, by(totdiab) nolntime plot1opts(msymbol(none)) plot2opts(msymbol(none))
graph export "${paper_figures}/Appendix5_HAALSI_loglog_unadjusted_${date}.png", as(png) replace

* Adjusted
stphplot, by(totdiab) nolntime adjustfor(i.agecat i.gender i.educ i.z_econtert i.smoker b2.bmicat) plot1opts(msymbol(none)) ///
          plot2opts(msymbol(none))
graph export "${paper_figures}/Appendix5_HAALSI_loglog_adjusted_${date}.png", as(png) replace
restore

** HRS
svyset h_cluster [pw = h_weight], strata(h_strata)

* Overall
* MRR
svy, subpop(if nonmiss == 1 & study == 1 & inrange(followtime_m,25,9999999)): poisson censordied i.totdiab i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, base irr exposure(followtime_y)
local loc_x = r(table)[1,2]
putexcel B10 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel C10 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel D10 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel E10 = "`loc_est'"

* HR
preserve
stset followtime_y [pw = h_weight] if nonmiss == 1 & study == 1 & inrange(followtime_m,25,9999999), failure(censordied)
svy, subpop(if nonmiss == 1 & study == 1 & inrange(followtime_m,25,9999999)): stcox i.totdiab i.agecat i.gender i.educ i.h_econtert i.smoker b2.bmicat, base
local loc_x = r(table)[1,2]
putexcel F10 = `loc_x'
local loc_ll = r(table)[5,2]
putexcel G10 = `loc_ll'
local loc_ul = r(table)[6,2]
putexcel H10 = `loc_ul'
local loc_est = string(`loc_x',"%9.2f") + " (" + string(`loc_ll',"%9.2f") + " - " + string(`loc_ul',"%9.2f") + ")"
putexcel I10 = "`loc_est'"

* Check proportional hazards assumption
* Unadjusted
stphplot, by(totdiab) nolntime plot1opts(msymbol(none)) plot2opts(msymbol(none))
graph export "${paper_figures}/Appendix5_HRS_loglog_unadjusted_${date}.png", as(png) replace

* Adjusted
stphplot, by(totdiab) nolntime adjustfor(i.agecat i.gender i.educ i.h_econtert i.smoker i.bmicat) plot1opts(msymbol(none)) ///
          plot2opts(msymbol(none))
graph export "${paper_figures}/Appendix5_HRS_loglog_adjusted_${date}.png", as(png) replace
restore
*/
